﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace Quexcel.OpenXML
{
    internal class QueryParser
    {
        /**
         * select id, name from Employee where id > 10000 group by a
         **/
        private static string selectTableStringFormat =
                  @"select\s+(?<SelectClause>[^/]+)\s+"
                + @"from\s+table\s+(?<TableName>(\w+|\([^/]+))"
                + @"(\s+where\s+(?<WhereClause>[^/]+)\s+group\sby\s+(?<GroupBy>[^/]+)\s+order\sby\s+(?<OrderBy>[^/]+)"
                + @"|\s+where\s+(?<WhereClause>[^/]+)\s+group\sby\s+(?<GroupBy>[^/]+)"
                + @"|\s+where\s+(?<WhereClause>[^/]+)\s+order\sby\s+(?<OrderBy>[^/]+)"
                + @"|\s+group\sby\s+(?<GroupBy>[^/]+)\s+order\sby\s+(?<OrderBy>[^/]+)"
                + @"|\s+where\s+(?<WhereClause>[^/]+)"
                + @"|\s+group\sby\s+(?<GroupBy>[^/]+)"
                + @"|\s+order\s+(?<OrderBy>[^/]+))?";

        private static string selectSheetStringFormat =
                  @"select\s+(?<SelectClause>[^/]+)\s+"
                + @"from\s+sheet\s+(?<TableName>(\w+|\([^/]+))"
                + @"(\s+where\s+(?<WhereClause>[^/]+)\s+group\sby\s+(?<GroupBy>[^/]+)\s+order\sby\s+(?<OrderBy>[^/]+)"
                + @"|\s+where\s+(?<WhereClause>[^/]+)\s+group\sby\s+(?<GroupBy>[^/]+)"
                + @"|\s+where\s+(?<WhereClause>[^/]+)\s+order\sby\s+(?<OrderBy>[^/]+)"
                + @"|\s+group\sby\s+(?<GroupBy>[^/]+)\s+order\sby\s+(?<OrderBy>[^/]+)"
                + @"|\s+where\s+(?<WhereClause>[^/]+)"
                + @"|\s+group\sby\s+(?<GroupBy>[^/]+)"
                + @"|\s+order\s+(?<OrderBy>[^/]+))?";

        /**
         * insert into Employee(id, name) values(1,'Ashish Jain')
         **/
        private static string insertTableStringFormat =
                  @"insert\s+into\s+table\s+(?<TableName>(\w+|\([^/]+))(\((?<InsertOrder>[^/]+)\))?"
                + @"\s+(" + selectTableStringFormat
                + @"|values\s*\((?<InsertValues>[^/]+)\))";

        private static string insertSheetStringFormat =
                  @"insert\s+into\s+sheet\s+(?<TableName>(\w+|\([^/]+))(\((?<InsertOrder>[^/]+)\))?"
                + @"\s+(" + selectSheetStringFormat
                + @"|values\s*\((?<InsertValues>[^/]+)\))";

        private static string resultString
            = "SelectClause:${SelectClause};"
              + "TableName:${TableName};"
              + "GroupBy:${WhereClause};"
              + "OrderBy:${OrderBy};"
              + "CellOrder:${InsertOrder};"
              + "CellValues:${InsertValues};";

        internal Regex SelectTablePattern { get; set; }
        internal Regex SelectSheetPattern { get; set; }
        internal Regex InsertTablePattern { get; set; }
        internal Regex InsertSheetPattern { get; set; }

        private static QueryParser parserInstance;

        private QueryParser()
        {

        }

        internal static QueryParser GetInstance()
        {
            if (parserInstance == null)
            {
                parserInstance = new QueryParser();
                parserInstance.SelectTablePattern = new Regex(selectTableStringFormat, RegexOptions.Compiled | RegexOptions.IgnoreCase);
                parserInstance.SelectSheetPattern = new Regex(selectSheetStringFormat, RegexOptions.Compiled | RegexOptions.IgnoreCase);
                parserInstance.InsertTablePattern = new Regex(insertTableStringFormat, RegexOptions.Compiled | RegexOptions.IgnoreCase);
                parserInstance.InsertSheetPattern = new Regex(insertSheetStringFormat, RegexOptions.Compiled | RegexOptions.IgnoreCase);
            }

            return parserInstance;
        }

        public QueryElements ParseQuery(String commandText)
        {
            String output = null;
            QueryElements elems = new QueryElements();

            if (InsertTablePattern.IsMatch(commandText))
            {
                output = InsertTablePattern.Match(commandText).Result(resultString);
                elems.Type = QueryType.InsertIntoTable;
            }
            else if (InsertSheetPattern.IsMatch(commandText))
            {
                output = InsertSheetPattern.Match(commandText).Result(resultString);
                elems.Type = QueryType.InsertIntoSheet;
            }
            else if (SelectTablePattern.IsMatch(commandText))
            {
                output = SelectTablePattern.Match(commandText).Result(resultString);
                elems.Type = QueryType.SelectFromTable;
            }
            else if (SelectSheetPattern.IsMatch(commandText))
            {
                output = SelectSheetPattern.Match(commandText).Result(resultString);
                elems.Type = QueryType.SelectFromSheet;
            }

            foreach (string elem in output.Split(';'))
            {
                if (elem.Trim() != "")
                {
                    string name = elem.Split(':')[0].Trim();
                    string value = elem.Split(':')[1].Trim();

                    if (value == String.Format("${{0}}", name)
                        || value == "")
                        value = null;

                    switch (name)
                    {
                        case "SelectClause":
                            elems.SelectClause = value;
                            break;
                        case "TableName":
                            elems.TableName = value;
                            break;
                        case "WhereClause":
                            elems.WhereClause = value;
                            break;
                        case "GroupBy":
                            elems.GroupBy = value;
                            break;
                        case "OrderBy":
                            elems.OrderBy = value;
                            break;
                        case "CellOrder":
                            elems.InsertOrder = value;
                            break;
                        case "CellValues":
                            elems.InsertValues = value;
                            break;
                    }
                }
            }

            return elems;
        }
    }

    internal struct QueryElements
    {
        public QueryType Type;
        public string SelectClause;
        public string TableName;
        public string WhereClause;
        public string GroupBy;
        public string OrderBy;
        public string InsertOrder;
        public string InsertValues;
    }

    internal enum QueryType
    {
        InsertIntoTable,
        InsertIntoSheet,
        Delete,
        Update,
        Alter,
        SelectFromTable,
        SelectFromSheet,
        Create
    }
}
